The rpivotTable package is an R htmlwidget built around the pivottable library.
PivotTable.js is a Javascript Pivot Table visualization library with drag’n’drop functionality built on top of jQuery / jQueryUI and written in CoffeeScript (then compiled to JavaScript) by Nicolas Kruchten at Datacratic. It is available under a MIT license
Many thanks to everyone that contributes bugs and prs, and of course thanks to Nicolas Kruchten for PivotTable.js.
The rpivotTable package depends on htmlwidgets package, so you need to install both packages. You can do this using the devtools package as follows:
Or directly from CRAN:
Call the package with
Just plug in your data.frame, table or
data.table to rpivotTable().
It is as simple as this:
## Warning in readLines(con, warn = readLines.warn): incomplete final line found
## on '/Users/jonasdefreitas/Git/rpivotTable/inst/htmlwidgets/rpivotTable.yaml'
The pivot table should appear in your RStudio Viewer or your browser of choice.
For additional technical information please refer to the examples and explanations here.
rpivotTable parameters decide how the pivot table will
look like the firs time it is opened:
data can be a data.frame
table or data.table. Nothing else is needed.
If only the data is selected the pivot table opens with nothing on rows
and columns (but you can at any time drag and drop any variable in rows
or columns at your leasure)rows and cols allow the user to create a
report, i.e. to indicate which element will be on rows and columns.aggregatorName indicates the type of aggregation.
Options here are numerous: Count, Count Unique Values, List Unique
Values, Sum, Integer Sum, Average, Sum over Sum, 80% Upper Bound, 80%
Lower Bound, Sum as Fraction of Total, Sum as Fraction of Rows, Sum as
Fraction of Columns, Count as Fraction of Total, Count as Fraction of
Rows, Count as Fraction of Columnsvals specifies the variable to use with
aggregatorName (if any).renderers dictates the type of graphic rendering used
for display, like Table, Treemap etc.sorters allow to implement a javascript function to
specify the ad hoc sorting of certain values. See vignette for an
example. It is especially useful with time divisions like days of the
week or months of the year (where the alphabetical order does not
work)For example, to display a table with frequency of colour combinations of eyes and hair, you can specify:
library(rpivotTable)
data(HairEyeColor)
rpivotTable(data = HairEyeColor, rows = "Hair",cols="Eye", vals = "Freq", aggregatorName = "Sum", rendererName = "Table", width="100%", height="400px")## Warning in readLines(con, warn = readLines.warn): incomplete final line found
## on '/Users/jonasdefreitas/Git/rpivotTable/inst/htmlwidgets/rpivotTable.yaml'
This will display the resulting table. Switching the
aggregatorName to Sum as Fraction of Rows will
give the row percentages (and the column totals will give the
percentages over the gran total).
To display the Hair values in reverse order:
library(rpivotTable)
data(HairEyeColor)
rpivotTable(data = HairEyeColor, rows = "Hair",cols="Eye", vals = "Freq", aggregatorName = "Sum", rendererName = "Table", sorters = "
function(attr) {
var sortAs = $.pivotUtilities.sortAs;
if (attr == \"Hair\") { return sortAs([\"Red\", \"Brown\", \"Blond\", \"Black\"]); }
}", width="100%", height="400px")## Warning in readLines(con, warn = readLines.warn): incomplete final line found
## on '/Users/jonasdefreitas/Git/rpivotTable/inst/htmlwidgets/rpivotTable.yaml'
This function could be useful for example to sort time divisions like months of the year or days of the week in their proper, non alphabetical order (thanks to palatinuse for its implementation).
You can also use the very visual new subtotals:
data(mtcars)
rpivotTable(mtcars,rows="gear", cols=c("cyl","carb"),subtotals=TRUE, width="100%", height="400px")## Warning in readLines(con, warn = readLines.warn): incomplete final line found
## on '/Users/jonasdefreitas/Git/rpivotTable/inst/htmlwidgets/rpivotTable.yaml'
Or if you want to include it as part of your dplyr /
magrittr pipeline, you can do that also.
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
# )
iris %>%
tbl_df %>%
filter( Sepal.Width > 3 & Sepal.Length > 5 ) %>%
rpivotTable(rows="Sepal.Width", rendererName="Treemap")## Warning: `tbl_df()` was deprecated in dplyr 1.0.0.
## ℹ Please use `tibble::as_tibble()` instead.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was generated.
## Warning in readLines(con, warn = readLines.warn): incomplete final line found
## on '/Users/jonasdefreitas/Git/rpivotTable/inst/htmlwidgets/rpivotTable.yaml'